package top.shiyiri.dbutils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import top.shiyiri.bean.Customer;
import top.shiyiri.bean.User;
import top.shiyiri.util.JDBCUtils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @author Aunean
 * @date 2022/1/20 20:02
 */
/*
commons-dbutils 是 Apache 组织提供的一个开源的 JDBC 工具类库，封装了针对于数据库的增删改查操作
 */
public class QueryRunnerTest {

    @Test
    public void test1() {
        QueryRunner runner = new QueryRunner();
        Connection connection = null;
        try {
            connection = JDBCUtils.getConnection3();

            String sql = "insert into customers(name, email, birth) values(?,?,?)";
            runner.update(connection, sql, "都是", "234@190.com", new Date(234234L));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, null);
        }
    }

    /*
    测试查询
    BeanHandler：ResultSetHandler接口的实现类，用于封装表中的一条记录
     */
    @Test
    public void test2() {
        QueryRunner runner = new QueryRunner();
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();

            String sql = "select * from user where id = ?";
            BeanHandler<User> handler = new BeanHandler<>(User.class);
            User query = runner.query(conn, sql, handler, 1);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }


    /*
    BeanListHandler：ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合
     */
    @Test
    public void test3() {
        QueryRunner runner = new QueryRunner();
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();

            String sql = "select * from user where id > ?";
            BeanListHandler<User> handler = new BeanListHandler<>(User.class);
            List<User> users = runner.query(conn, sql, handler, 1);
            users.forEach(System.out::println);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /*
    MapHandler：ResultSetHandler接口的实现类，对应表中的一条记录
     */
    @Test
    public void test4() {
        QueryRunner runner = new QueryRunner();
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();

            String sql = "select * from user where id = ?";
            MapHandler handler = new MapHandler();
            Map<String, Object> map = runner.query(conn, sql, handler, 1);
            System.out.println(map);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /*
    MapListHandler：ResultSetHandler接口的实现类，对应表中的多条记录
     */
    @Test
    public void test5() {
        QueryRunner runner = new QueryRunner();
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();

            String sql = "select * from user where id > ?";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> maps = runner.query(conn, sql, handler, 1);
            maps.forEach(System.out::println);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /*
    ScalarHandler：用于查询特殊值
     */
    @Test
    public void test6() {
        QueryRunner runner = new QueryRunner();
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();

            String sql = "select count(*) from user";
            ScalarHandler handler = new ScalarHandler();
            Object query = runner.query(conn, sql, handler);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }

    /*
    自定义 ResultSetHandler 的实现类
     */
    @Test
    public void test7() {
        QueryRunner runner = new QueryRunner();
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection3();

            String sql = "select id,name,email,birth from customers where id = ?";

            ResultSetHandler<Customer> handler = rs -> {
                if (rs.next()) {
                    int id = rs.getInt("id");
                    String email = rs.getString("email");
                    String name = rs.getString("name");
                    Date birthday = rs.getDate("birth");
                    return new Customer(id, name, email, birthday);
                }
                return null;
            };

            Customer query = runner.query(conn, sql, handler, 5);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null);
        }
    }
}
